#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 分析设备类型与实例表的对应关系...\n');

// 获取所有设备类型
const devices = db.prepare(`
  SELECT DISTINCT 
    d.device_id,
    d.classification_code,
    d.specialty,
    dtm.english_name
  FROM devices d
  LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
  ORDER BY d.specialty, d.classification_code
`).all();

console.log('设备类型总数:', devices.length);

// 获取所有实例表
const instanceTables = db.prepare(`
  SELECT name FROM sqlite_master 
  WHERE type='table' AND name LIKE 'device_instances_%'
  ORDER BY name
`).all();

console.log('实例表总数:', instanceTables.length);
console.log();

// 专业前缀映射
const specialtyPrefixes = {
  '配管专业': 'piping',
  '电气专业': 'electrical',
  '暖通专业': 'hvac',
  '通信专业': 'communication',
  '仪表专业': 'instrumentation'
};

// 分析设备类型到表名的映射
const typeToTableMapping = new Map();
const tableToDevicesMapping = new Map();

devices.forEach(device => {
  const { device_id, classification_code, specialty, english_name } = device;
  
  // 生成表名
  let tableName;
  if (english_name && specialtyPrefixes[specialty]) {
    tableName = `device_instances_${specialtyPrefixes[specialty]}_${english_name}`;
  } else {
    tableName = `device_instances_${device_id.replace(/-/g, '_')}`;
  }
  
  typeToTableMapping.set(device_id, tableName);
  
  if (!tableToDevicesMapping.has(tableName)) {
    tableToDevicesMapping.set(tableName, []);
  }
  tableToDevicesMapping.get(tableName).push(device);
});

console.log('📊 重复表名分析:');

// 找出共享同一个表的设备类型
let sharedTables = 0;
let totalDevicesInSharedTables = 0;

tableToDevicesMapping.forEach((devices, tableName) => {
  if (devices.length > 1) {
    sharedTables++;
    totalDevicesInSharedTables += devices.length;
    console.log(`\n🔗 表 ${tableName} 被 ${devices.length} 个设备类型共享:`);
    devices.forEach(device => {
      console.log(`   - ${device.device_id} (${device.classification_code})`);
    });
  }
});

console.log(`\n📈 统计结果:`);
console.log(`- 设备类型总数: ${devices.length}`);
console.log(`- 实例表总数: ${instanceTables.length}`);
console.log(`- 共享表数量: ${sharedTables}`);
console.log(`- 共享表中的设备数: ${totalDevicesInSharedTables}`);
console.log(`- 独立表数量: ${instanceTables.length - sharedTables}`);

console.log(`\n🧮 数学验证:`);
console.log(`- 独立表 + 共享表 = ${instanceTables.length - sharedTables} + ${sharedTables} = ${instanceTables.length}`);
console.log(`- 独立设备 + 共享设备 = ${devices.length - totalDevicesInSharedTables} + ${totalDevicesInSharedTables} = ${devices.length}`);

// 按专业分析
console.log(`\n🏢 按专业分析:`);
const specialtyStats = {};
devices.forEach(device => {
  const specialty = device.specialty;
  if (!specialtyStats[specialty]) {
    specialtyStats[specialty] = {
      deviceCount: 0,
      tableNames: new Set()
    };
  }
  
  specialtyStats[specialty].deviceCount++;
  
  // 计算对应的表名
  let tableName;
  if (device.english_name && specialtyPrefixes[specialty]) {
    tableName = `device_instances_${specialtyPrefixes[specialty]}_${device.english_name}`;
  } else {
    tableName = `device_instances_${device.device_id.replace(/-/g, '_')}`;
  }
  
  specialtyStats[specialty].tableNames.add(tableName);
});

Object.entries(specialtyStats).forEach(([specialty, stats]) => {
  console.log(`\n${specialty}:`);
  console.log(`  - 设备类型数: ${stats.deviceCount}`);
  console.log(`  - 实例表数: ${stats.tableNames.size}`);
  console.log(`  - 压缩比: ${(stats.deviceCount / stats.tableNames.size).toFixed(2)}:1`);
});

db.close();